package com.accenture.adf.newcodington.module20.activity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * Class that demonstrates usage of JOIN for retrieving data from ZOO and EVENT tables in mySQL database
 */
public class ZooEventsDao {

	// Connection from JDBC API for establishing connection to underlying
	// database
	Connection connection = null;
	// PreparedStatement is used to execute pre-compiled SQL queries for JDBC
	// insertions
	PreparedStatement statement = null;
	// Resultset object to store data retrieved from query. 
	ResultSet resultSet=null;
	// Constant string that captures QUERY to be executed by JDBC statements
	static String DISPLAYQUERY = "select z.zooname, z.animalcount, e.eventname, e.description, e.duration, e.schedule, e.ticketprice " +
			" from zoo z, event e, place p where z.placeid=e.placeid and e.placeid=p.placeid and z.zooname=?";

	

	/**
	 * Method to capture event details for a particular ZOO and information will be
	 * retrieved by providing ZOONAME and returns data in String format. 
	 * @param zooname
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public String displayEventsOfZoo(String zooname) throws SQLException,ClassNotFoundException
	{
		connection=ConnectionToDB.getConnection();
		statement=connection.prepareStatement(DISPLAYQUERY); // Running SQL query to display date using Join
		statement.setString(1, zooname);
		resultSet=statement.executeQuery(); // capturing data in RESULTSET object
		String eventData="";
		while(resultSet.next())
		{
			// Storing data in eventData String based on COLUMN NAMES returned by the query
			eventData=eventData+resultSet.getString("ZOONAME")+" | "+
						resultSet.getString("ANIMALCOUNT")+" | "+
						resultSet.getString("EVENTNAME")+" | "+
						resultSet.getString("DESCRIPTION")+" | "+
						resultSet.getInt("DURATION")+" | "+
						resultSet.getString("SCHEDULE")+" | "+
						resultSet.getString("TICKETPRICE")+" \n ";
		}
		resultSet.close();
		ConnectionToDB.closeConnection();
		return eventData;
	}
	/**
	 * MAIN method for executing the JOIN
	 * @param args
	 */
	public static void main(String[] args) {
		
		ZooEventsDao dao=new ZooEventsDao();
		String eventsName=""; // Declaring local variable
		try
		{
		eventsName=dao.displayEventsOfZoo("SouthTown Zoo"); // passing ZOONAME to the method to capture EVENTS mapped to that ZOO 
		}
		catch (SQLException e) // handles the EXCEPTION for SQL queries
		{
			System.out
					.println("Error while insertion due to problem in SQL query..");
			System.out.println("Error is :" + e.getMessage());
		} catch (ClassNotFoundException e) // handles exceptions for mySQL
											// DRIVER
		{
			System.out
					.println("Error while insertion due to Driver inavailability..");
			System.out.println("Error is :" + e.getMessage());
		}
		System.out.println("Events is :\n"+eventsName);
		
	}
	
}
